﻿#include "sqliteoperator.h"

#include <vector>
#include <QApplication>

// 构造函数中初始化数据库对象，并建立数据库
SqliteOperator::SqliteOperator()
{
    if (QSqlDatabase::contains("qt_sql_default_connection"))
    {
        database = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        // 建立和SQlite数据库的连接
        database = QSqlDatabase::addDatabase("QSQLITE");
            QString application_path = QApplication::applicationFilePath();//找到应用的目录
        QString applicationDirPath= QApplication::applicationDirPath();
        qDebug()<< applicationDirPath;
        // 设置数据库文件的名字
        database.setDatabaseName(applicationDirPath +"/main.db");

    }
}

// 打开数据库
bool SqliteOperator::openDb()
{
    if (!database.open())
    {
        qDebug() << "Error: Failed to connect database." << database.lastError();
    }
    else
    {
        // do something
    }

    return true;
}

// 创建数据表
void SqliteOperator::createTable()
{
    // 用于执行sql语句的对象
    QSqlQuery sqlQuery;
    // 构建创建数据库的sql语句字符串
    //interval_type:间隔，1：一次性，2：每天，3：每周，4：每月
    //name : 任务名称
    //task_time:关机时间
    QString createSql = QString("CREATE TABLE task_main (\
                                id INTEGER PRIMARY KEY,\
                                name TEXT,\
                                task_time INT NOT NULL,\
                                interval_type INT NOT NULL,\
                                create_time INT NOT NULL,\
                                status_flag INT NOT NULL,\
                                mode INT NOT NULL)");
                        sqlQuery.prepare(createSql);
    // 执行sql语句
    if(!sqlQuery.exec())
    {
        qDebug() << "Error: Fail to create table. " << sqlQuery.lastError();
    }
    else
    {
        qDebug() << "Table created!";
    }
}

// 判断数据库中某个数据表是否存在
bool SqliteOperator::isTableExist(QString& tableName)
{
    QSqlDatabase database = QSqlDatabase::database();
    if(database.tables().contains(tableName))
    {
        return true;
    }

    return false;
}

// 查询全部数据
void SqliteOperator::queryTable(QList<TaskMain> &taskMainList)
{
    QSqlQuery sqlQuery;
    sqlQuery.exec("SELECT * FROM task_main");
    if(!sqlQuery.exec())
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            int id = sqlQuery.value("id").toInt();
            QString name = sqlQuery.value("name").toString();
            long long taskTime = sqlQuery.value("task_time").toLongLong();
            int intervalType = sqlQuery.value("interval_type").toInt();
            long long createTime = sqlQuery.value("create_time").toLongLong();
            int mode =sqlQuery.value("mode").toInt();
//            qDebug()<<QString("id:%1    name:%2    age:%3").arg(id).arg(name).arg(intervalType);
            TaskMain taskMain ;
            taskMain.id = id;
            taskMain.taskTime=taskTime;
            taskMain.intervalType=intervalType;
            taskMain.mode=mode;
            taskMainList.push_back(taskMain);
        }
    }
}

// 插入单条数据
void SqliteOperator::singleInsertData(TaskMain &singledb)
{
    QSqlQuery sqlQuery;
    sqlQuery.prepare("INSERT INTO task_main(name,task_time,interval_type,create_time,status_flag,mode)"
                     " VALUES(:name,:task_time,:interval_type,:create_time,:status_flag,:mode)");
//    sqlQuery.bindValue(":id", singledb.id);
    sqlQuery.bindValue(":name", singledb.name);
    sqlQuery.bindValue(":task_time", singledb.taskTime);
    sqlQuery.bindValue(":interval_type", singledb.intervalType);
    sqlQuery.bindValue(":create_time", singledb.createTime);
    sqlQuery.bindValue(":status_flag", singledb.statusFlag);
    sqlQuery.bindValue(":mode", singledb.mode);
    if(!sqlQuery.exec())
    {
        qDebug() << "Error: Fail to insert data. " << sqlQuery.lastError();
    }
    else
    {
        // do something
    }
}

// 插入多条数据
void SqliteOperator::moreInsertData(QList<TaskMain>& moredb)
{
    // 进行多个数据的插入时，可以利用绑定进行批处理
    QSqlQuery sqlQuery;
    sqlQuery.prepare("INSERT INTO task_main VALUES(?,?,?)");
    QVariantList idList,nameList,ageList;
    for(int i=0; i< moredb.size(); i++)
    {
        idList <<  moredb.at(i).id;
        nameList << moredb.at(i).name;
        ageList << moredb.at(i).taskTime;
    }
    sqlQuery.addBindValue(idList);
    sqlQuery.addBindValue(nameList);
    sqlQuery.addBindValue(ageList);

    if (!sqlQuery.execBatch()) // 进行批处理，如果出错就输出错误
    {
        qDebug() << sqlQuery.lastError();
    }
}

// 修改数据
void SqliteOperator::modifyData(int id, QString name, int age)
{
    QSqlQuery sqlQuery;
    sqlQuery.prepare("UPDATE task_main SET name=?,age=? WHERE id=?");
    sqlQuery.addBindValue(name);
    sqlQuery.addBindValue(age);
    sqlQuery.addBindValue(id);
    if(!sqlQuery.exec())
    {
        qDebug() << sqlQuery.lastError();
    }
    else
    {
        qDebug() << "updated data success!";
    }
}

// 删除数据
void SqliteOperator::deleteData(int id)
{
    QSqlQuery sqlQuery;

    sqlQuery.exec(QString("DELETE FROM task_main WHERE id = %1").arg(id));
    if(!sqlQuery.exec())
    {
        qDebug()<<sqlQuery.lastError();
    }
    else
    {
        qDebug()<<"deleted data success!";
    }
}

//删除数据表
void SqliteOperator::deleteTable(QString& tableName)
{
    QSqlQuery sqlQuery;

    sqlQuery.exec(QString("DROP TABLE %1").arg(tableName));
    if(sqlQuery.exec())
    {
        qDebug() << sqlQuery.lastError();
    }
    else
    {
        qDebug() << "deleted table success";
    }
}

void SqliteOperator::closeDb(void)
{
    database.close();
}
